#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -ureadonly -preadonly123 -h192.168.0.49<<EOF
use jiayou;
select from_unixtime(created,'%Y-%m-%d')as days,enterprise_id,
sum(case when tactics_type = 'score' then 1 else 0 end) as score_allmoney,
sum(case when tactics_type = 'order' then 1 else 0 end) as order_allmoney,
sum(case when tactics_type = 'reissued' then 1 else 0 end) as reissued_allmoney,
sum(case when tactics_type like 'sunny%' then 1 else 0 end) as other_allmoney,

sum(case when tactics_type = 'score' and coupon_statue = 3 then 1 else 0 end) as score_usedmoney,
sum(case when tactics_type = 'order' and coupon_statue = 3 then 1 else 0 end) as order_usedmoney,
sum(case when tactics_type = 'reissued' and coupon_statue = 3 then 1 else 0 end) as reissued_usedmoney,
sum(case when tactics_type like 'sunny%' and coupon_statue = 3 then 1 else 0 end) as other_usedmoney
from coupon_info where coupon_type = 2 and from_unixtime(created,'%Y-%m-%d')='$dt'
group by from_unixtime(created,'%Y-%m-%d'),enterprise_id ;
EOF